package com.maszy.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.maszy.bean.StaffLogin;
import com.maszy.commons.tools.MD5;
import com.maszy.db.DBConnection;

public class StaffLoginService {
	StaffLogin service = new StaffLogin();
	private static PreparedStatement pst;
	private static ResultSet rs = null;

	public int add(StaffLogin obj) {
		DBConnection con = new DBConnection();
		int result = 0;
		String sql = "insert into Maszy_StaffLogin(Maszy_StaffLogin_Id,Maszy_StaffLogin_LoginName,Maszy_StaffLogin_LoginPassword,Maszy_StaffLogin_IP,Maszy_StaffLogin_LastTime,Maszy_Log_NoteFlag,Maszy_StaffLogin_Remark) values(?,?,?,?,?,?,?)";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			pst.setLong(1, obj.getStaffLogin_Id());
			pst.setString(2, obj.getStaffLogin_LoginName());
			pst.setString(3, obj.getStaffLogin_LoginPassword());
			pst.setString(4, obj.getStaffLogin_IP());
			pst.setString(5, obj.getStaffLogin_LastTime());
			pst.setInt(6, obj.getLog_NoteFlag());
			pst.setString(7, obj.getStaffLogin_Remark());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return result;
	}

	public int update(StaffLogin obj, String method) {
		DBConnection con = new DBConnection();
		int i = 0;
		if ("login".equals(method)) {
			// 登陆，修改登陆IP及最后登陆时间
			String sql = "update Maszy_StaffLogin set Maszy_StaffLogin_IP=?,Maszy_StaffLogin_LastTime=? where Maszy_StaffLogin_Id=?";
			try {
				pst = con.getConnection().prepareStatement(sql);
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try {
				pst.setString(1, obj.getStaffLogin_IP());
				pst.setString(2, obj.getStaffLogin_LastTime());
				pst.setLong(3, obj.getStaffLogin_Id());
				i = pst.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				try {
					if (rs != null) {
						rs.close();
						rs = null;
					}
					if (con != null) {
						con.close();
						con = null;
					}
				} catch (Exception e) {
					// TODO 自动生成 catch 块
					e.printStackTrace();
				}
			}
		} else if ("update".equals(method)) {
			// 修改登陆帐号或密码
			String sql = "update Maszy_StaffLogin set Maszy_StaffLogin_Name=?,Maszy_StaffLogin_LoginPassword=?,Maszy_StaffLogin_NoteFlag=?,Maszy_StaffLogin_Remark=? where Maszy_StaffLogin_Id=?";
			try {
				pst = con.getConnection().prepareStatement(sql);
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try {
				pst.setString(1, obj.getStaffLogin_LoginName());
				pst.setString(2, obj.getStaffLogin_LoginPassword());
				pst.setInt(3, obj.getLog_NoteFlag());
				pst.setString(4, obj.getStaffLogin_Remark());
				pst.setLong(5, obj.getStaffLogin_Id());
				i = pst.executeUpdate();

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (rs != null) {
						rs.close();
						rs = null;
					}
					if (con != null) {
						con.close();
						con = null;
					}
				} catch (Exception e) {
					// TODO 自动生成 catch 块
					e.printStackTrace();
				}

			}
		}
		return i;
	}

	public int delete(StaffLogin obj) {
		DBConnection con = new DBConnection();
		int i = 0;
		String sql = "delete from  Maszy_StaffLogin  where Maszy_StaffLogin_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setLong(1, obj.getStaffLogin_Id());
			i = pst.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}

	public StaffLogin queryById(StaffLogin obj) {
		DBConnection con = new DBConnection();
		String sql = "select * from Maszy_StaffLogin where Maszy_StaffLogin_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setLong(1, obj.getStaffLogin_Id());
			rs = pst.executeQuery();
			while (rs.next()) {
				obj = new StaffLogin();
				obj.setStaffLogin_Id(rs.getLong("Maszy_StaffLogin_Id"));
				obj.setStaffLogin_LoginName(rs
						.getString("Maszy_StaffLogin_LoginName"));
				obj.setStaffLogin_IP(rs.getString("Maszy_StaffLogin_IP"));
				obj.setStaffLogin_LastTime(rs
						.getString("Maszy_StaffLogin_LastTime"));
				obj.setLog_NoteFlag(rs.getInt("Maszy_StaffLogin_NoteFlag"));
				obj.setStaffLogin_Remark(rs
						.getString("Maszy_StaffLogin_Remark"));

			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj;
	}

	public List queryAll() {
		List list = new ArrayList();
		DBConnection con = new DBConnection();
		String sql = "select Maszy_Staff.Maszy_Staff_Id,Maszy_Staff.Maszy_Staff_Name,maszy_stafflogin.* from maszy_staff left join maszy_stafflogin on maszy_staff.maszy_staff_id=maszy_stafflogin.maszy_stafflogin_id";
		try {
			pst = con.getConnection().prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					if ("Maszy_StaffLogin_LoginName".equals(rsmd
							.getColumnName(i))) {
						if (rs.getObject(i) == null) {
							map.put(rsmd.getColumnName(i), "帐号未启用");
						} else {
							map.put(rsmd.getColumnName(i), rs.getObject(i));
						}

					} else {
						map.put(rsmd.getColumnName(i), rs.getObject(i));
					}

				}
				list.add(map);
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}

	// 用户登陆

	public static StaffLogin adminLogin(String name,String password){
		StaffLogin obj = new StaffLogin();
		DBConnection con = new DBConnection();
		try {
			
			String sql = "select Maszy_StaffLogin.*,Maszy_Staff.* from Maszy_StaffLogin,Maszy_Staff where Maszy_StaffLogin.Maszy_StaffLogin_LoginName=? and Maszy_StaffLogin.Maszy_StaffLogin_LoginPassword=? and Maszy_Staff.Maszy_Staff_Id=Maszy_StaffLogin.Maszy_StaffLogin_Id";
			pst = con.getConnection().prepareStatement(sql);
			pst.setString(1, name);
			pst.setString(2, password);
			rs = pst.executeQuery();
			while (rs.next()) {						
				obj.setStaffLogin_Id(rs.getLong("Maszy_StaffLogin_Id"));
				obj.setStaffLogin_LoginName(rs.getString("Maszy_StaffLogin_LoginName"));
				obj.setStaffLogin_IP(rs.getString("Maszy_StaffLogin_IP"));
				obj.setStaffLogin_LastTime(rs.getString("Maszy_StaffLogin_LastTime"));
				obj.setLog_NoteFlag(rs.getInt("Maszy_Log_NoteFlag"));
				obj.setStaffLogin_Remark(rs.getString("Maszy_StaffLogin_Remark"));
				obj.setStaff_Name(rs.getString("Maszy_Staff_Name"));
				obj.setDoorStore_Id(rs.getInt("Maszy_DoorStore_Id"));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj;
	}
}
